//202102104015柴亚楠
package cn.edu.sdjzu.xg.kcsj.dao;

import cn.edu.sdjzu.xg.kcsj.domain.StudentClass;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class
StudentClassDao {
    private static StudentClassDao studentClassDao;

    private StudentClassDao() {
    }

    public static StudentClassDao getInstance() {
        if (studentClassDao == null) {
            studentClassDao = new StudentClassDao();
        }
        return studentClassDao;
    }

    /**
     * @param conn   数据库连接对象
     * @return desiredStudents
     * @throws SQLException
     */
    public Collection<StudentClass> findAll(Pagination pagination, List<Condition> conditionList, Connection conn) throws SQLException {
        Collection<StudentClass> studentClasses = new HashSet<StudentClass> ();
        StringBuilder sql_select = new StringBuilder("SELECT * FROM studentClass");
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        int totalNum = CommonDao.getInstance().count(conn, "StudentClass");
        if (pagination != null) {
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }
        PreparedStatement pstmt = conn.prepareStatement(sql_select.toString ());
        ResultSet resultSet = pstmt.executeQuery();
        while (resultSet.next()) {
            StudentClass studentClass = new StudentClass(
                    resultSet.getInt("id"),
                    resultSet.getString("name"),
                    resultSet.getString("no"));
            studentClasses.add(studentClass);
        }
        JdbcHelper.close(resultSet, pstmt);
        return studentClasses;
    }

    /**
     * @param conn   数据库连接对象
     * @param id 查询条件
     * @return desiredStudent
     * @throws SQLException
     */
    public StudentClass find(Integer id,Connection conn) throws SQLException {
        String studentClasses_sql = "select * from StudentClass where id = ?";
        PreparedStatement pstmt = conn.prepareStatement(studentClasses_sql);
        pstmt.setInt(1, id);
        ResultSet resultSet = pstmt.executeQuery();
        resultSet.next();
        StudentClass studentClass = new StudentClass(resultSet.getInt("id"), resultSet.getString("name")
                , resultSet.getString("no"));
        JdbcHelper.close(resultSet,pstmt);
        return studentClass;
    }

    /**
     * @param conn   数据库连接对象
     * @param studentClass 修改的目标对象
     * @return 影响行数（是否修改成功）
     * @throws SQLException
     */

    public boolean update(StudentClass studentClass,Connection conn) throws SQLException {
        String updateStudentClass_sql = "UPDATE studentclass SET name = ?,no= ? WHERE id = ?";
        PreparedStatement pstmt = conn.prepareStatement(updateStudentClass_sql);
        pstmt.setString(1, studentClass.getName());
        pstmt.setString(2, studentClass.getNo());
        pstmt.setInt(3, studentClass.getId());
        int affectedRowNum = pstmt.executeUpdate();
        JdbcHelper.close(null,pstmt);
        return affectedRowNum > 0;
    }

    /**
     * @param conn   数据库连接对象
     * @param studentClass 增加条件
     * @return 影响行数（是否增加成功）
     * @throws SQLException
     */
    public boolean add(StudentClass studentClass,Connection conn) throws SQLException {
        String addStudentClass_sql = "INSERT INTO Studentclass(name,no) VALUES" + "(?,?)";
        PreparedStatement pstmt = conn.prepareStatement(addStudentClass_sql);
        pstmt.setString(1, studentClass.getName());
        pstmt.setString(2, studentClass.getNo());
        int affectedRowNum = pstmt.executeUpdate();
        JdbcHelper.close(null,pstmt);
        return affectedRowNum >0;
    }

    /**
     * @param conn   数据库连接对象
     * @param id 删除条件
     * @return 影响行数（是否删除成功）
     * @throws SQLException
     */
    public boolean delete(Integer id,Connection conn) throws SQLException {
        String deleteStudentClass_sql = "delete from Studentclass where id = ?";
        PreparedStatement pstmt = conn.prepareStatement(deleteStudentClass_sql);
        pstmt.setInt(1, id);
        int affectedRowNum = pstmt.executeUpdate();
        JdbcHelper.close(null,pstmt);
        return affectedRowNum >0;
    }
}
